package com.luyuan.crm.util;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Map;
import java.util.Set;

import javax.faces.context.FacesContext;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;

/**
 * Excel写操作帮助类
 */
public class ExcelUtil {
    private static final Logger log = Logger.getLogger(ExcelUtil.class);

    /**
     * 功能：将HSSFWorkbook写入Excel文件
     * 
     * @param wb
     *            HSSFWorkbook
     * @param wbName
     *            文件名
     */
    public static void writeWorkbook(HSSFWorkbook wb, String fileName) {
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(fileName);
            wb.write(fos);
        } catch (FileNotFoundException e) {
            log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
        } catch (IOException e) {
            log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
        } finally {
            try {
                if (fos != null) {
                    fos.close();
                }
            } catch (IOException e) {
                log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
            }
        }
    }

    /**
     * 功能：创建HSSFSheet工作簿
     * 
     * @param wb
     *            HSSFWorkbook
     * @param sheetName
     *            String
     * @return HSSFSheet
     */
    public static HSSFSheet createSheet(HSSFWorkbook wb, String sheetName) {
        HSSFSheet sheet = wb.createSheet(sheetName);
        sheet.setDefaultColumnWidth((short) 12);
        sheet.setGridsPrinted(false);
        sheet.setDisplayGridlines(false);
        return sheet;
    }

    /**
     * 功能：创建HSSFRow
     * 
     * @param sheet
     *            HSSFSheet
     * @param rowNum
     *            int
     * @param height
     *            int
     * @return HSSFRow
     */
    public static HSSFRow createRow(HSSFSheet sheet, int rowNum) {
        HSSFRow row = sheet.createRow(rowNum);
        return row;
    }

    /**
     * 功能：创建HSSFRow
     * 
     * @param sheet
     *            HSSFSheet
     * @param rowNum
     *            int
     * @param height
     *            int
     * @return HSSFRow
     */
    public static HSSFRow createRow(HSSFSheet sheet, int rowNum, int height) {
        HSSFRow row = sheet.createRow(rowNum);
        row.setHeight((short) height);
        return row;
    }

    public static HSSFCellStyle getNormalCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setWrapText(true);
        return cellStyle;
    }

    /**
     * 功能：创建CellStyle样式
     * 
     * @param wb
     *            HSSFWorkbook
     * @param backgroundColor
     *            背景色
     * @param foregroundColor
     *            前置色
     * @param font
     *            字体
     * @return CellStyle
     */
    public static HSSFCellStyle createCellStyle(HSSFWorkbook wb, short backgroundColor, short foregroundColor,
            short halign, HSSFFont font) {
        HSSFCellStyle cs = wb.createCellStyle();
        cs.setAlignment(halign);
        cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cs.setFillBackgroundColor(backgroundColor);
        cs.setFillForegroundColor(foregroundColor);
        cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cs.setFont(font);
        return cs;
    }

    /**
     * 功能：创建带边框的CellStyle样式
     * 
     * @param wb
     *            HSSFWorkbook
     * @param backgroundColor
     *            背景色
     * @param foregroundColor
     *            前置色
     * @param font
     *            字体
     * @return CellStyle
     */
    public static HSSFCellStyle createBorderCellStyle(HSSFWorkbook wb, short backgroundColor, short foregroundColor,
            short halign, HSSFFont font) {
        HSSFCellStyle cs = wb.createCellStyle();
        cs.setAlignment(halign);
        cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cs.setFillBackgroundColor(backgroundColor);
        cs.setFillForegroundColor(foregroundColor);
        cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cs.setFont(font);
        cs.setBorderLeft(HSSFCellStyle.BORDER_DASHED);
        cs.setBorderRight(HSSFCellStyle.BORDER_DASHED);
        cs.setBorderTop(HSSFCellStyle.BORDER_DASHED);
        cs.setBorderBottom(HSSFCellStyle.BORDER_DASHED);
        return cs;
    }

    public static HSSFRow createHeader(HSSFRow row, Map<Integer, String> headerMap) {
        Set<Integer> keys = headerMap.keySet();
        for (Integer i : keys) {
            HSSFCell cell = row.createCell(i.shortValue());
            cell.setCellValue(headerMap.get(i));
        }
        return row;
    }

    /**
     * 功能：创建CELL
     * 
     * @param row
     *            HSSFRow
     * @param cellNum
     *            int
     * @return HSSFCell
     */
    public static HSSFCell createCell(HSSFRow row, short cellNum) {
        HSSFCell cell = row.createCell(cellNum);
        return cell;
    }

    /**
     * 功能：合并单元格
     * 
     * @param sheet
     *            HSSFSheet
     * @param firstRow
     *            int
     * @param lastRow
     *            int
     * @param firstColumn
     *            int
     * @param lastColumn
     *            int
     * @return int 合并区域号码
     */

    public static int mergeCell(HSSFSheet sheet, int rowFrom, short colFrom, int rowTo, short colTo) {
        return sheet.addMergedRegion(new Region(rowFrom, colFrom, rowTo, colTo));
    }

    /**
     * 功能：创建CELL
     * 
     * @param row
     *            HSSFRow
     * @param cellNum
     *            int
     * @param style
     *            HSSFStyle
     * @return HSSFCell
     */
    public static HSSFCell createCell(HSSFRow row, short cellNum, HSSFCellStyle style) {
        HSSFCell cell = row.createCell(cellNum);
        cell.setCellStyle(style);
        return cell;
    }

    /**
     * 功能：创建字体
     * 
     * @param wb
     *            HSSFWorkbook
     * @param boldweight
     *            short
     * @param color
     *            short
     * @return Font
     */
    public static HSSFFont createFont(HSSFWorkbook wb, short boldweight, short color, short size) {
        HSSFFont font = wb.createFont();
        font.setBoldweight(boldweight);
        font.setColor(color);
        font.setFontHeightInPoints(size);
        return font;
    }

    public static void exportExcel(HSSFWorkbook wb, String fileName) throws IOException {
        FacesContext ctx = FacesContext.getCurrentInstance();
        HttpServletResponse response = (HttpServletResponse) ctx.getExternalContext().getResponse();
        response.setContentType("application/msexcel");
        response.setHeader("name", fileName);
        response.setHeader("Content-disposition", "attachment; filename=\"" + URLEncoder.encode(fileName, "UTF-8")
                + "\"");

        OutputStream out = response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
        ctx.responseComplete();
    }
}